package com.kcs.fxmargin.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

import com.kcs.common.utils.Constant;
import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.model.TRateHeader;

public class TRateHeaderDAO {
	private Logger log = Logger.getLogger(Constant.LOG_FX);
	
	public int insert(TRateHeader model, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		int res = -1;
		
		try{
			sql.append(" insert into t_rate_header ");
			sql.append("  ( id,as_of_date,time,file_path,message,active_status,create_by, ");
			sql.append("    create_dt, row_version ) ");
			sql.append(" values (?, ?, ?, ?, ?, ?, ?, getdate(), ?) ");
		
			pstmt = conn.prepareStatement(sql.toString());
				
			int ind = 1;	
			
			pstmt.setString(ind++, model.getId());
			pstmt.setDate(ind++, model.getAsOfDate());
			pstmt.setTime(ind++, model.getTime());
			pstmt.setString(ind++,model.getFilePath());
			pstmt.setString(ind++,model.getMessage());
			pstmt.setString(ind++, model.getActiveStatus());
			pstmt.setString(ind++, model.getCreateBy());
			pstmt.setInt(ind++, model.getRowVersion());
								
			res = pstmt.executeUpdate();

			
		}catch(Exception e){
			log.error(e.getMessage());
			e.printStackTrace();
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return res;
	}
	
	public int deleteRateHeader(Connection conn, String id) throws Exception {
		PreparedStatement pstm = null;
		
		StringBuffer sql = new StringBuffer();
		int delete = -1;
		
		sql.append(" delete from t_rate_header where id = ? ");
		
		log.debug("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, id);
		delete = pstm.executeUpdate();
		
		log.debug("delete = " + delete);
		
		return delete;
		
	}
	
	public int updateByCondition(TRateHeader model, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		int res = -1;
		
		try{
			sql.append(" update t_rate_header ");
			sql.append(" set update_by = ? ");
			sql.append("    ,update_dt = getdate() ");
			if(commonUtils.isNotEmpty(model.getMessage())){
				sql.append(" ,message = ? ");
			}
			if(commonUtils.isNotEmpty(model.getActiveStatus())){
				sql.append(" ,active_status = ? ");
			}
			if(commonUtils.isNotEmpty(model.getRowVersion())){
				sql.append(" ,row_version = row_version+1");
			}
			sql.append(" where ");
			boolean isExistClause = false;
			if(commonUtils.isNotEmpty(model.getAsOfDate())){
				sql.append(" as_of_date = ? ");
				isExistClause = true;
			}
			if(commonUtils.isNotEmpty(model.getTime())){
				sql.append(isExistClause?" and convert(time(0),time,103) = convert(time(0),?,103)  ":" convert(time(0),time,103) = convert(time(0),?,103) ");
			}
			if(commonUtils.isNotEmpty(model.getId())){
				sql.append(isExistClause?" and id = ? ":" id = ? ");
			}
			pstmt = conn.prepareStatement(sql.toString());
			
			int i =1;
			
			pstmt.setString(i++, model.getUpdateBy());
			//log.info("sql="+sql.toString());
			if(commonUtils.isNotEmpty(model.getMessage())){
				pstmt.setString(i++, model.getMessage());
			}
			if(commonUtils.isNotEmpty(model.getActiveStatus())){
				pstmt.setString(i++, model.getActiveStatus());
			}
			if(commonUtils.isNotEmpty(model.getAsOfDate())){
				pstmt.setDate(i++, model.getAsOfDate());
			}
			if(commonUtils.isNotEmpty(model.getTime())){
				pstmt.setTime(i++, model.getTime());
			}
			if(commonUtils.isNotEmpty(model.getId())){
				pstmt.setString(i++, model.getId());
			}

			res = pstmt.executeUpdate();
			
		}catch(Exception e){
			log.error(e.getMessage());
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return res;
	}
	
	
	public TRateHeader getCurrentActiveHeader(TRateHeader model,Connection conn) throws Exception{
		
		PreparedStatement pstmt 	= null;
		ResultSet rs 			= null;
		StringBuffer sql 		= new StringBuffer();
		TRateHeader header		= null;
		
		try{
			sql.append(" select id,as_of_date,convert(time(0),time,103) time,file_path,active_status,message,create_by,create_dt,update_by,update_dt,row_version ");
			sql.append(" from t_rate_header ");
			sql.append(" where active_status =  ? ");
			sql.append(" and as_of_date = ? ");
//		    sql.append(" and convert(time(0),time,103) = convert(time(0),?,103)  ");
			sql.append(" and convert(time(0),time,103) <= convert(time(0),?,103)  ");
			
			pstmt = conn.prepareStatement(sql.toString());
			int i =1;

			pstmt.setString(i++, Constant.ACTIVE_STATUS);
			pstmt.setDate(i++, model.getAsOfDate());
			pstmt.setTime(i++, model.getTime());
			
			rs   = pstmt.executeQuery();
			
			if(rs.next()){
				header = new TRateHeader();
				header.setId(rs.getString("id"));
				header.setAsOfDate(rs.getDate("as_of_date"));
				header.setTime(rs.getTime("time"));

			}
			
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		} finally {
			if(pstmt != null) pstmt.close();
			if(rs != null) rs.close();
		}
		
		return header;
	}
	
	
	public boolean isExistHeader(TRateHeader model,Connection conn) throws Exception{
		
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		StringBuffer sql 		= new StringBuffer();
		boolean isExist			= false;
		
		try{
			sql.append(" select 'x' ");
			sql.append(" from t_rate_header ");
			sql.append(" where ( as_of_date = ? ");
		    sql.append("        and convert(time(0),time,103) = convert(time(0),?,103) ) ");
		    sql.append(" and active_status =  ? ");
			
			pstmt = conn.prepareStatement(sql.toString());
			int i =1;
			pstmt.setDate(i++, model.getAsOfDate());
			pstmt.setTime(i++, model.getTime());
			pstmt.setString(i++, Constant.ACTIVE_STATUS);
			rs   = pstmt.executeQuery();
			
			if(rs.next()){
				isExist = true;

			}
			
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		} finally {
			if(pstmt != null) pstmt.close();
			if(rs != null) rs.close();
		}
		
		return isExist;
	}
	
	public boolean isOlderAsOfDateAndTime(TRateHeader model,Connection conn) throws Exception{
		
		PreparedStatement pstmt 	= null;
		ResultSet rs 			= null;
		StringBuffer sql 		= new StringBuffer();
//		TRateHeader header		= null;
		boolean isOlder = false;
		try{
		    
		    sql.append(" select as_of_date ,convert(time(0),time,103) time ");
		    sql.append(" from t_rate_header ");
		    sql.append(" where (as_of_date >= ? and convert(time(0),time,103) > convert(time(0),?,103) ) ");
		    sql.append(" and active_status =  ? ");		    
	    	    
			pstmt = conn.prepareStatement(sql.toString());
			int i =1;
			pstmt.setDate(i++, model.getAsOfDate());
			pstmt.setTime(i++, model.getTime());
			pstmt.setString(i++, Constant.ACTIVE_STATUS);

			rs   = pstmt.executeQuery();

			if(rs.next()){

				isOlder = true;
			}
			
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		} finally {
			if(pstmt != null) pstmt.close();
			if(rs != null) rs.close();
		}
		
		return isOlder;
	}
	
	public void callSPRevaluationRate(Connection conn)throws Exception{
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		log.info("Begin : TRateHeaderDAO : call: SPRevaluationRate");
		
		try{
			sql = "{call SPRevaluationRate()}";
			
				log.info(sql);
			
				cstmt = conn.prepareCall(sql);
				cstmt.execute();
			
		}catch(Exception e){
			throw e;
		}finally{
			if(cstmt != null){
				try{
					cstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		log.info("End : TRateHeaderDAO : SPRevaluationRate");
	}
}
